package com.asistencia.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.asistencia.dao.interfaces.StudentDao;
import com.asistencia.domain.Career;
import com.asistencia.domain.Student;

@Repository
@SuppressWarnings({ "rawtypes", "unchecked" })
public class StudentJdbc implements StudentDao {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public Student login(String studentCode, String password)throws DataAccessException {
		
	String SQL ="SELECT idstudent,student_code, name, lastname,mother_lastname,email,dni,idcarreer FROM student WHERE student_code = ? AND password = ? LIMIT 1";
		
	List matches=jdbcTemplate.query(SQL, new Object[]{studentCode, password }, new RowMapper(){
	
	@Override
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		Student student = new Student();
		Career career= new Career();
		
		student.setIdStudent(rs.getInt("idstudent"));
		student.setName(rs.getString("name"));
		student.setLastname(rs.getString("lastname"));
		student.setMotherLastname(rs.getString("mother_lastname"));
		student.setEmail(rs.getString("email"));
		student.setDni(rs.getString("dni"));
		student.setStudentCode(rs.getString("student_code"));
		career.setIdCareer(rs.getInt("idcarreer"));
		student.setMyCareer(career);
		
		return student;

	}});
	return (matches.size() > 0 ? (Student) matches.get(0) : null);}

	@Override
	public Student getById(Integer idStudent) throws DataAccessException {
		String SQL ="SELECT idstudent,student_code, name, lastname,mother_lastname,email,dni,idcarreer FROM student WHERE idstudent= ? LIMIT 1 ";
		List matches = jdbcTemplate.query(SQL, new Object[]{idStudent}, new RowMapper(){
	
	@Override
	public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
		Student student = new Student();
		Career career= new Career();
		
		student.setIdStudent(rs.getInt("idstudent"));
		student.setName(rs.getString("name"));
		student.setLastname(rs.getString("lastname"));
		student.setMotherLastname(rs.getString("mother_lastname"));
		student.setEmail(rs.getString("email"));
		student.setDni(rs.getString("dni"));
		student.setStudentCode(rs.getString("student_code"));
		career.setIdCareer(rs.getInt("idcarreer"));
		student.setMyCareer(career);
		
		return student;
	}
	});
		return (matches.size() > 0 ? (Student) matches.get(0) : null);
	}

	@Override
	public List<Student> listAll() throws DataAccessException {
		String SQL= "SELECT * FROM student ";
		List matches=jdbcTemplate.query(SQL, new RowMapper() {
		
		@Override
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
			Student student = new Student();
			Career career= new Career();
			
			student.setIdStudent(rs.getInt("idstudent"));
			student.setName(rs.getString("name"));
			student.setLastname(rs.getString("lastname"));
			student.setMotherLastname(rs.getString("mother_lastname"));
			student.setEmail(rs.getString("email"));
			student.setDni(rs.getString("dni"));
			student.setStudentCode(rs.getString("student_code"));
			career.setIdCareer(rs.getInt("idcarreer"));
			student.setMyCareer(career);
			
			return student;
		}
		});
		
		return (matches.size() > 0 ? (List<Student>) matches : null);
	}

	@Override
	public List<Student> listByName(String name) throws DataAccessException {
		String SQL ="SELECT * FROM student WHERE name LIKE ? OR lastname LIKE ? ";
		List matches = jdbcTemplate.query(SQL,new Object []{"%"+name+"%", "%" +name+"%"}, new RowMapper(){
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
				Student student = new Student();
				Career career= new Career();
				
				student.setIdStudent(rs.getInt("idstudent"));
				student.setName(rs.getString("name"));
				student.setLastname(rs.getString("lastname"));
				student.setMotherLastname(rs.getString("mother_lastname"));
				student.setEmail(rs.getString("email"));
				student.setDni(rs.getString("dni"));
				student.setStudentCode(rs.getString("student_code"));
				career.setIdCareer(rs.getInt("idcarreer"));
				student.setMyCareer(career);
				
				return student;
			}});
		return (matches.size() > 0 ? (List<Student>) matches : null);
			
		
	}

	@Override
	public List<Student> listByCareer(Integer idcarreer)throws DataAccessException {
		String SQL="SELECT * FROM student  WHERE idcarreer =?";
		List matches=jdbcTemplate.query(SQL,new Object []{idcarreer}, new RowMapper(){
			
			@Override
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Student student = new Student();
				Career career= new Career();
				
				student.setIdStudent(rs.getInt("idstudent"));
				student.setName(rs.getString("name"));
				student.setLastname(rs.getString("lastname"));
				student.setMotherLastname(rs.getString("mother_lastname"));
				student.setEmail(rs.getString("email"));
				student.setDni(rs.getString("dni"));
				student.setStudentCode(rs.getString("student_code"));
				career.setIdCareer(rs.getInt("idcarreer"));
				student.setMyCareer(career);
				
				return student;
		}});
		return (matches.size() > 0 ? (List<Student>) matches : null);
}}
	
